﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entities;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class ClienteDAL : DBController
    {
        public ClienteDAL()
        { 
        
        }
         //TODO: SE ESTA ASIGNANDO 2 VECES EL COMMAND TEXT
        public ClienteEntity GetClienteByUsernameAndPassword(UsuarioEntity user)
        {
            try
            {
                ClienteEntity cliente = new ClienteEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClienteByUsernameAndPassword", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetClienteByUsernameAndPassword";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@username", user.VarUsername);
                    dCmd.Parameters.AddWithValue("@password", user.VarPassword);

                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    dr.Read();
                }
                return cliente;

            }catch(Exception ex){
                throw ex;
            }
        }



        /// <summary>
        /// Funcion que devuelve los clientes
        /// </summary>
        /// <returns></returns>
        public List<ClienteEntity> GetClientes()
        {
            try
            {
                List<ClienteEntity> clientes = new List<ClienteEntity>();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spgetclientes", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetClientes";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            //ClienteEntity cliente = new ClienteEntity();
                            //cliente.IntIdCliente = (int)dr.GetValue(0);
                            //cliente.VarNombre = (string)dr.GetValue(1);
                            //cliente.VarApellido = (string)dr.GetValue(2);
                            //cliente.BigDNI = long.Parse(dr.GetValue(3).ToString());
                            //cliente.VarDireccion = (string)dr.GetValue(4);
                            //cliente.BigTelefono = long.Parse(dr.GetValue(5).ToString());
                            //cliente.VarMail = (string)dr.GetValue(6);
                            //cliente.DatFecha_Nac = DateTime.Parse(dr.GetValue(7).ToString());
                            //cliente.IntCodigoPostal = (dr.GetValue(8).ToString() != string.Empty) ? Int32.Parse(dr.GetValue(8).ToString()) : 0;
                            //cliente.IntIdUsuario = Int32.Parse(dr.GetValue(9).ToString());
                            //cliente.FloatSaldo = float.Parse(dr.GetValue(10).ToString());
                            //cliente.BitIsDeleted = (bool)dr.GetValue(11);

                            ClienteEntity cliente = this.BuildCliente(dr);

                            clientes.Add(cliente);
                        }
                    }
                }
                return clientes;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Funcion que agrega un rol
        /// </summary>
        /// <returns></returns>
        public Entities.ClienteEntity AddCliente(Entities.ClienteEntity client)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;
                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spAddCliente", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spAddCliente";

                    dCmd.CommandType = CommandType.StoredProcedure;
  
                    dCmd.Parameters.AddWithValue("@varNombre", client.VarNombre);
                    dCmd.Parameters.AddWithValue("@varApellido", client.VarApellido);
                    dCmd.Parameters.AddWithValue("@bigDNI", client.BigDNI);
                    dCmd.Parameters.AddWithValue("@varDireccion", client.VarDireccion);
                    dCmd.Parameters.AddWithValue("@bigTelefono", client.BigTelefono);
                    dCmd.Parameters.AddWithValue("@varMail", client.VarMail);
                    dCmd.Parameters.AddWithValue("@datFecha_Nac", client.DatFecha_Nac);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", client.IntCodigoPostal);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    dr.Read();

                    client.IntIdCliente = Int32.Parse(dr.GetValue(0).ToString()); 
                    client.IntIdUsuario = Int32.Parse(dr.GetValue(1).ToString());

                }
                return client;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




        /// <summary>
        /// Funcion que cambia un cliente A proveedor
        /// </summary>
        /// <returns></returns>
        public Entities.ProveedorEntity CambiarClienteAProveedor(Entities.ProveedorEntity prov, Entities.ClienteEntity cli)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;
                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spCambiarClienteAProveedor", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spCambiarClienteAProveedor";

                    dCmd.CommandType = CommandType.StoredProcedure;
            
                    dCmd.Parameters.AddWithValue("@intIdCliente", cli.IntIdCliente);
                    dCmd.Parameters.AddWithValue("@varRS", prov.VarRS);
                    dCmd.Parameters.AddWithValue("@varDom", prov.VarDom);
                    dCmd.Parameters.AddWithValue("@intTelefono", prov.IntTelefono);
                    dCmd.Parameters.AddWithValue("@intIdCiudad", prov.IntIdCiudad);
                    dCmd.Parameters.AddWithValue("@varCUIT", prov.VarCUIT);
                    dCmd.Parameters.AddWithValue("@intIdRubro", prov.IntIdRubro);
                    dCmd.Parameters.AddWithValue("@varMail", prov.VarMail);
                    dCmd.Parameters.AddWithValue("@varNombreContacto", prov.VarNombreContacto);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", prov.IntCodigoPostal);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    dr.Read();

                    prov.IntIdProveedor = Int32.Parse(dr.GetValue(0).ToString());
                    prov.IntIdUsuario = Int32.Parse(dr.GetValue(1).ToString());

                }
                return prov;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Funcion que devuelve los clientes
        /// </summary>
        /// <returns></returns>
        public ClienteEntity GetClienteById(ClienteEntity _cliente)
        {
            try
            {
                ClienteEntity cliente = new ClienteEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClienteById", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetClienteById";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@intClienteId", _cliente.IntIdCliente);


                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    dr.Read();

                    cliente.IntIdCliente = (int)dr.GetValue(0);
                    cliente.VarNombre = (string)dr.GetValue(1);
                    cliente.VarApellido = (string)dr.GetValue(2);
                    cliente.BigDNI = long.Parse(dr.GetValue(3).ToString());
                    cliente.VarDireccion = (string)dr.GetValue(4);
                    cliente.BigTelefono = long.Parse(dr.GetValue(5).ToString());
                    cliente.VarMail = (string)dr.GetValue(6);
                    cliente.DatFecha_Nac = DateTime.Parse(dr.GetValue(7).ToString());
                    cliente.IntCodigoPostal = (dr.GetValue(8).ToString() != string.Empty) ? Int32.Parse(dr.GetValue(8).ToString()) : 0;
                    cliente.FloatSaldo = float.Parse(dr.GetValue(9).ToString());
                    cliente.IntIdUsuario = Int32.Parse(dr.GetValue(10).ToString());
                    cliente.BitIsDeleted = (bool)dr.GetValue(11);
                }
                    
                return cliente;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }





        /// <summary>
        /// Funcion que devuelve los clientes
        /// </summary>
        /// <returns></returns>
        public ClienteEntity GetClientesByDNI(ClienteEntity _cliente)
        {
            try
            {
                ClienteEntity cliente = new ClienteEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClientesByDNI", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetClientesByDNI";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@bigDNI", _cliente.BigDNI);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    if(dr.HasRows){
                        dr.Read();

                        cliente.IntIdCliente = (int)dr.GetValue(0);
                        cliente.VarNombre = (string)dr.GetValue(1);
                        cliente.VarApellido = (string)dr.GetValue(2);
                        cliente.BigDNI = long.Parse(dr.GetValue(3).ToString());
                        cliente.VarDireccion = (string)dr.GetValue(4);
                        cliente.BigTelefono = long.Parse(dr.GetValue(5).ToString());
                        cliente.VarMail = (string)dr.GetValue(6);
                        cliente.DatFecha_Nac = DateTime.Parse(dr.GetValue(7).ToString());
                        cliente.IntCodigoPostal = (dr.GetValue(8).ToString() != string.Empty) ? Int32.Parse(dr.GetValue(8).ToString()) : 0;
                        cliente.IntIdUsuario = Int32.Parse(dr.GetValue(9).ToString());
                        cliente.FloatSaldo = float.Parse(dr.GetValue(10).ToString());
                        cliente.BitIsDeleted = (bool)dr.GetValue(11);
                    }
                }

                return cliente;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Funcion que devuelve los clientes
        /// </summary>
        /// <returns></returns>
        public ClienteEntity GetClientesByTelefono(ClienteEntity _cliente)
        {
            try
            {
                ClienteEntity cliente = new ClienteEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClientesByTelefono", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetClientesByTelefono";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@bigTelefono", _cliente.BigTelefono);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    if (dr.HasRows)
                    {
                        dr.Read();

                        cliente.IntIdCliente = (int)dr.GetValue(0);
                        cliente.VarNombre = (string)dr.GetValue(1);
                        cliente.VarApellido = (string)dr.GetValue(2);
                        cliente.BigDNI = long.Parse(dr.GetValue(3).ToString());
                        cliente.VarDireccion = (string)dr.GetValue(4);
                        cliente.BigTelefono = long.Parse(dr.GetValue(5).ToString());
                        cliente.VarMail = (string)dr.GetValue(6);
                        cliente.DatFecha_Nac = DateTime.Parse(dr.GetValue(7).ToString());
                        cliente.IntCodigoPostal = (dr.GetValue(8).ToString() != string.Empty) ? Int32.Parse(dr.GetValue(8).ToString()) : 0;
                        cliente.IntIdUsuario = Int32.Parse(dr.GetValue(9).ToString());
                        cliente.FloatSaldo = float.Parse(dr.GetValue(10).ToString());
                        cliente.BitIsDeleted = (bool)dr.GetValue(11);
                    }
                }

                return cliente;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Funcion que actualiza un cliente
        /// </summary>
        /// <returns></returns>
        public bool UpdateCliente(Entities.ClienteEntity client)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spUpdateCliente", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spUpdateCliente";

                    dCmd.CommandType = CommandType.StoredProcedure;
                    

                    dCmd.Parameters.AddWithValue("@intIdCliente", client.IntIdCliente);
                    dCmd.Parameters.AddWithValue("@varNombre", client.VarNombre);
                    dCmd.Parameters.AddWithValue("@varApellido", client.VarApellido);
                    dCmd.Parameters.AddWithValue("@varDireccion", client.VarDireccion);
                    dCmd.Parameters.AddWithValue("@bigTelefono", client.BigTelefono);
                    dCmd.Parameters.AddWithValue("@varMail", client.VarMail);
                    dCmd.Parameters.AddWithValue("@datFecha_Nac", client.DatFecha_Nac);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", client.IntCodigoPostal);
                    dCmd.Parameters.AddWithValue("@bitIsDeleted", client.BitIsDeleted);
                    
                    dCmd.ExecuteNonQuery();
                    dCmd.Dispose();
                }
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Funcion que devuelve los clientes filtrado
        /// </summary>
        /// <returns></returns>
        public List<ClienteEntity> GetClientesFiltre(ClienteEntity _cliente)
        {
            try
            {
                List<ClienteEntity> clientes = new List<ClienteEntity>();
                using (SqlConnection conn = this.OpenConn())
                {
                    //Armamos la query
                    string query = string.Empty;

                    query += "SELECT cl.intIdCliente, cl.varNombre, cl.varApellido, cl.bigDNI, cl.varDireccion, cl.bigTelefono, cl.varMail, cl.datFecha_Nac, cl.intCodigoPostal,cl.intIdUsuario, cl.floatSaldo, cl.bitIsDeleted FROM MARAVILLA_SQL.Clientes cl WHERE 1=1";
                    //Aplicamos los filtros
                    if (_cliente.VarNombre != null && _cliente.VarNombre != string.Empty)
                    {
                        query += " AND cl.varNombre LIKE '%" + _cliente.VarNombre +"%'";
                    }

                    if (_cliente.VarApellido != null && _cliente.VarApellido != string.Empty)
                    {
                        query += " AND cl.varApellido LIKE '%" + _cliente.VarApellido + "%'";
                    }

                    if (_cliente.VarMail != null && _cliente.VarMail != string.Empty)
                    {
                        query += " AND cl.varMail LIKE '%" + _cliente.VarMail + "%'";
                    }

                    if (/*_cliente.BigDNI != null &&*/ _cliente.BigDNI != 0)
                    {
                        query += " AND cl.bigDNI = '" + _cliente.BigDNI.ToString() + "'";
                    }
                    
                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClientes", conn);

                    dCmd.CommandText = query;

                    dCmd.CommandType = CommandType.Text;


                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            ClienteEntity cliente = new ClienteEntity();
                            cliente.IntIdCliente = (int)dr.GetValue(0);
                            cliente.VarNombre = (string)dr.GetValue(1);
                            cliente.VarApellido = (string)dr.GetValue(2);
                            cliente.BigDNI = long.Parse(dr.GetValue(3).ToString());
                            cliente.VarDireccion = (string)dr.GetValue(4);
                            cliente.BigTelefono = long.Parse(dr.GetValue(5).ToString());
                            cliente.VarMail = (string)dr.GetValue(6);
                            cliente.DatFecha_Nac = DateTime.Parse(dr.GetValue(7).ToString());
                            cliente.IntCodigoPostal = (dr.GetValue(8).ToString() != string.Empty) ? Int32.Parse(dr.GetValue(8).ToString()) : 0;
                            cliente.IntIdUsuario = Int32.Parse(dr.GetValue(9).ToString());
                            cliente.FloatSaldo = float.Parse(dr.GetValue(10).ToString());
                            cliente.BitIsDeleted = (bool)dr.GetValue(11);

                            clientes.Add(cliente);
                        }
                    }
                }
                return clientes;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Funcion que borra un cliente
        /// </summary>
        /// <returns></returns>
        public bool DeleteCliente(Entities.ClienteEntity cliente)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spDeleteCliente", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spDeleteCliente";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@intIdCliente", cliente.IntIdCliente);

                    // dCmd.ExecuteNonQuery();
                    dCmd.ExecuteNonQuery();
                    dCmd.Dispose();
                }
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ClienteEntity GetClienteByUserId(int userId)
        {
            ClienteEntity cliente = new ClienteEntity();

            using (SqlConnection conn = this.OpenConn())
            {
                SqlDataReader reader;
                SqlCommand command = new SqlCommand("maravilla_sql.spgetclientebyuserid", conn);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Userid", userId);

                reader = command.ExecuteReader();
                command.Dispose();

                if (reader.Read())
                {
                    cliente = this.BuildCliente(reader);
                }
            }

            return cliente;
        }

        private ClienteEntity BuildCliente(SqlDataReader dr)
        {
            ClienteEntity cliente = new ClienteEntity();
            int i;

            i = dr.GetOrdinal("intIdCliente");
            if (!dr.IsDBNull(i))
                cliente.IntIdCliente = dr.GetInt32(i);

            i = dr.GetOrdinal("varNombre");
            if (!dr.IsDBNull(i))
                cliente.VarNombre = dr.GetString(i);

            i = dr.GetOrdinal("varApellido");
            if (!dr.IsDBNull(i))
                cliente.VarApellido = dr.GetString(i);

            i = dr.GetOrdinal("bigDNI");
            if (!dr.IsDBNull(i))
                cliente.BigDNI = Convert.ToInt64(dr[i]);

            i = dr.GetOrdinal("varDireccion");
            if (!dr.IsDBNull(i))
                cliente.VarDireccion = dr.GetString(i);

            i = dr.GetOrdinal("bigTelefono");
            if (!dr.IsDBNull(i))
                cliente.BigTelefono = Convert.ToInt64(dr[i]);

            i = dr.GetOrdinal("varMail");
            if (!dr.IsDBNull(i))
                cliente.VarMail = dr.GetString(i);

            i = dr.GetOrdinal("datFecha_Nac");
            if (!dr.IsDBNull(i))
                cliente.DatFecha_Nac = dr.GetDateTime(i);

            i = dr.GetOrdinal("intCodigoPostal");
            if (!dr.IsDBNull(i))
                cliente.IntCodigoPostal = (dr[i].ToString() != string.Empty) ? Int32.Parse(dr[i].ToString()) : 0;

            i = dr.GetOrdinal("intIdUsuario");
            if (!dr.IsDBNull(i))
                cliente.IntIdUsuario = dr.GetInt32(i);

            i = dr.GetOrdinal("floatSaldo");
            if (!dr.IsDBNull(i))
                cliente.FloatSaldo = (float)Convert.ToDouble(dr[i]);

            i = dr.GetOrdinal("bitIsDeleted");
            if (!dr.IsDBNull(i))
                cliente.BitIsDeleted = dr.GetBoolean(i);

            i = dr.GetOrdinal("varUsername");
            if (!dr.IsDBNull(i))
                cliente.VarUsername = dr.GetString(i);

            return cliente;
        }

        public TarjetaEntity GetTarjetaByClienteId(int idCliente)
        {
            TarjetaEntity tarjeta = new TarjetaEntity();

            using (SqlConnection conn = this.OpenConn())
            {
                SqlDataReader reader;
                SqlCommand command = new SqlCommand("maravilla_sql.spGetTarjetaByClientId", conn);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@idcli", idCliente);

                reader = command.ExecuteReader();
                command.Dispose();

                //Me interesa solo el primer registro, por mas que traiga muchos
                if (reader.Read())
                {
                    tarjeta.Numero = Convert.ToInt32(reader["numero"]);
                    tarjeta.CodigoVerificador = Convert.ToInt32(reader["codigo"]);
                    tarjeta.Tipo = reader["tipo"].ToString();
                }
            }

            return tarjeta;
        }

        public int AddTarjeta(int nroTarjeta, string tipoTarjeta, int codigoVerificador)
        {
            int idTarjeta;

            using (SqlConnection conn = this.OpenConn())
            {
                SqlCommand command = new SqlCommand("maravilla_sql.spAddTarjeta", conn);

                command.CommandType = CommandType.StoredProcedure;
                 //Parametros de entrada
                command.Parameters.AddWithValue("@nrotarjeta", nroTarjeta);
                command.Parameters.AddWithValue("@tipotarjeta", tipoTarjeta);
                command.Parameters.AddWithValue("@codigoverificador", codigoVerificador);
                
                //Parametros de salida
                SqlParameter paramOut = new SqlParameter("@idtarjeta", SqlDbType.Int);
                paramOut.Direction = ParameterDirection.Output;
                command.Parameters.Add(paramOut);

                command.ExecuteNonQuery();
                command.Dispose();

                idTarjeta = Convert.ToInt32(command.Parameters["@idtarjeta"].Value);
            }

            return idTarjeta;
        }

        public void ExecuteCarga(string fechaActual, float monto, int intIdCliente, int tipoPago, int idTarjeta)
        {
            using (SqlConnection conn = this.OpenConn())
            {
                SqlCommand command = new SqlCommand("maravilla_sql.spExecuteCarga", conn);

                command.CommandType = CommandType.StoredProcedure;
                //Parametros de entrada
                command.Parameters.AddWithValue("@monto", monto);
                command.Parameters.AddWithValue("@idcliente", intIdCliente);
                command.Parameters.AddWithValue("@tipopago", tipoPago);
                command.Parameters.AddWithValue("@idtarjeta", idTarjeta);

                SqlParameter param = new SqlParameter("@fecha", fechaActual);
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                param.Direction = ParameterDirection.Input;

                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public int GetComprasByCupon(string codigoCupon, int idCliente)
        {
            int cant = 0;

            using (SqlConnection conn = this.OpenConn())
            {
                SqlDataReader reader;
                SqlCommand command = new SqlCommand("maravilla_sql.spGetComprasByCupon", conn);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@idcli", idCliente);
                command.Parameters.AddWithValue("@codigoCupon", codigoCupon);

                reader = command.ExecuteReader();
                command.Dispose();

                if (reader.Read())
                {
                    cant = Convert.ToInt32(reader["cantidad"]);
                }
            }

            return cant;
        }

        public void ConfirmCompra(string codigoCupon, int idCliente, string fechaActual)
        {
            using (SqlConnection conn = this.OpenConn())
            {
                SqlCommand command = new SqlCommand("maravilla_sql.spConfirmCompra", conn);

                command.CommandType = CommandType.StoredProcedure;
                //Parametros de entrada
                command.Parameters.AddWithValue("@codigoCupon", codigoCupon);
                command.Parameters.AddWithValue("@idCliente", idCliente);

                SqlParameter param = new SqlParameter("@fecha", fechaActual);
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                param.Direction = ParameterDirection.Input;

                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public void ReturnCupon(DateTime fechaActual, int idCliente, string codigoCupon, string motivo)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {
                    SqlCommand command = new SqlCommand("maravilla_sql.spReturnCupon", conn);

                    command.CommandType = CommandType.StoredProcedure;
                    //Parametros de entrada
                    command.Parameters.AddWithValue("@fecha", fechaActual);
                    command.Parameters.AddWithValue("@codigoCupon", codigoCupon);
                    command.Parameters.AddWithValue("@idCliente", idCliente);
                    command.Parameters.AddWithValue("@motivo", motivo);

                    command.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("No puede devolver dos veces el mismo cupon", ex);
            }
        }
    }
}
